CREATE TABLE [dbo].[AW_Property] (
    [PropertyId]           UNIQUEIDENTIFIER NOT NULL,
    [RoadId]               UNIQUEIDENTIFIER NOT NULL,
    [ProjectId]            UNIQUEIDENTIFIER NULL,
    [PropertyTypeId]       UNIQUEIDENTIFIER NOT NULL,
    [UnitNumber]           NVARCHAR (100)   NOT NULL,
    [ListType]             TINYINT          NULL,
    [RTransactionId]       UNIQUEIDENTIFIER NULL,
    [STransactionId]       UNIQUEIDENTIFIER NULL,
    [SRAmt]                DECIMAL (18, 2)  NULL,
    [SRPSF]                DECIMAL (18, 2)  NULL,
    [SRAskAmt]             DECIMAL (18, 2)  NULL,
    [SRAskPSF]             DECIMAL (18, 2)  NULL,
    [SRCreateDate]         DATETIME         NULL,
    [SRYes]                TINYINT          NULL,
    [SCAmt]                DECIMAL (18, 2)  NULL,
    [SCPSF]                DECIMAL (18, 2)  NULL,
    [SCAskAmt]             DECIMAL (18, 2)  NULL,
    [SCAskPSF]             DECIMAL (18, 2)  NULL,
    [SCCreateDate]         DATETIME         NULL,
    [SCYes]                TINYINT          NULL,
    [SLAmt]                DECIMAL (18, 2)  NULL,
    [SLPSF]                DECIMAL (18, 2)  NULL,
    [SLAskAmt]             DECIMAL (18, 2)  NULL,
    [SLAskPSF]             DECIMAL (18, 2)  NULL,
    [SLCreateDate]         DATETIME         NULL,
    [SLYes]                TINYINT          NULL,
    [SIAmt]                DECIMAL (18, 2)  NULL,
    [SIPSF]                DECIMAL (18, 2)  NULL,
    [SIAskAmt]             DECIMAL (18, 2)  NULL,
    [SIAskPSF]             DECIMAL (18, 2)  NULL,
    [SICreateDate]         DATETIME         NULL,
    [SIYes]                TINYINT          NULL,
    [RRAmt]                DECIMAL (18, 2)  NULL,
    [RRPSF]                DECIMAL (18, 2)  NULL,
    [RRAskAmt]             DECIMAL (18, 2)  NULL,
    [RRAskPSF]             DECIMAL (18, 2)  NULL,
    [RRCreateDate]         DATETIME         NULL,
    [RRYes]                TINYINT          NULL,
    [RCAmt]                DECIMAL (18, 2)  NULL,
    [RCPSF]                DECIMAL (18, 2)  NULL,
    [RCAskAmt]             DECIMAL (18, 2)  NULL,
    [RCAskPSF]             DECIMAL (18, 2)  NULL,
    [RCCreateDate]         DATETIME         NULL,
    [RCYes]                TINYINT          NULL,
    [RLAmt]                DECIMAL (18, 2)  NULL,
    [RLPSF]                DECIMAL (18, 2)  NULL,
    [RLAskAmt]             DECIMAL (18, 2)  NULL,
    [RLAskPSF]             DECIMAL (18, 2)  NULL,
    [RLCreateDate]         DATETIME         NULL,
    [RLYes]                TINYINT          NULL,
    [RIAmt]                DECIMAL (18, 2)  NULL,
    [RIPSF]                DECIMAL (18, 2)  NULL,
    [RIAskAmt]             DECIMAL (18, 2)  NULL,
    [RIAskPSF]             DECIMAL (18, 2)  NULL,
    [RICreateDate]         DATETIME         NULL,
    [RIYes]                TINYINT          NULL,
    [SListerId]            UNIQUEIDENTIFIER NULL,
    [RListerId]            UNIQUEIDENTIFIER NULL,
    [KeyInOffice]          TINYINT          NULL,
    [ProjectRemark]        NVARCHAR (MAX)   NULL,
    [PropertyDesc]         NVARCHAR (MAX)   NULL,
    [SignboardStatus]      TINYINT          NULL,
    [SignboardWhere]       NVARCHAR (MAX)   NULL,
    [SignboardWhen]        DATETIME         NULL,
    [SurveySheet]          NVARCHAR (100)   NULL,
    [QuitRent]             NVARCHAR (20)    NULL,
    [AssessmentBill]       NVARCHAR (20)    NULL,
    [PendingStatus]        TINYINT          NULL,
    [PendingWho]           NVARCHAR (20)    NULL,
    [PendingWhen]          DATETIME         NULL,
    [PendingWhy]           NVARCHAR (MAX)   NULL,
    [RentPendingStatus]    TINYINT          NULL,
    [RentPendingWho]       NVARCHAR (20)    NULL,
    [RentPendingWhen]      DATETIME         NULL,
    [RentPendingWhy]       NVARCHAR (MAX)   NULL,
    [Tenure]               TINYINT          NOT NULL,
    [ListHoldExpired]      INT              NULL,
    [Access]               TINYINT          NULL,
    [AccessRemark]         NVARCHAR (MAX)   NULL,
    [Condition]            TINYINT          NULL,
    [ConditionRemark]      NVARCHAR (MAX)   NULL,
    [Facing]               TINYINT          NULL,
    [Zoning]               TINYINT          NOT NULL,
    [ZoningRemark]         NVARCHAR (MAX)   NULL,
    [Room]                 INT              NOT NULL,
    [Bathroom]             INT              NOT NULL,
    [BestBuy]              TINYINT          NULL,
    [ExclusiveStatus]      TINYINT          NULL,
    [ExclusiveWho]         NVARCHAR (20)    NULL,
    [ExclusiveWhen]        DATETIME         NULL,
    [ExclusiveEndDate]     DATETIME         NULL,
    [AuthorizeSaleRent]    TINYINT          NULL,
    [Remark]               NVARCHAR (MAX)   NULL,
    [Production1]          DECIMAL (18, 2)  NULL,
    [Production2]          DECIMAL (18, 2)  NULL,
    [Production3]          DECIMAL (18, 2)  NULL,
    [Admin1]               DECIMAL (18, 2)  NULL,
    [Admin2]               DECIMAL (18, 2)  NULL,
    [Admin3]               DECIMAL (18, 2)  NULL,
    [LAWidth]              DECIMAL (18, 2)  NOT NULL,
    [LALength]             DECIMAL (18, 2)  NOT NULL,
    [LASF]                 DECIMAL (18, 2)  NOT NULL,
    [BUWidth]              DECIMAL (18, 2)  NOT NULL,
    [BULength]             DECIMAL (18, 2)  NOT NULL,
    [BUSF]                 DECIMAL (18, 2)  NOT NULL,
    [FloorLoad]            NVARCHAR (50)    NULL,
    [SECMK]                NVARCHAR (50)    NULL,
    [Ceiling]              NVARCHAR (50)    NULL,
    [Terrain]              NVARCHAR (50)    NULL,
    [District]             NVARCHAR (50)    NULL,
    [Lot]                  NVARCHAR (50)    NULL,
    [Phase]                NVARCHAR (50)    NULL,
    [Furnished]            TINYINT          NOT NULL,
    [FurnishedRemark]      NVARCHAR (MAX)   NULL,
    [Renovated]            TINYINT          NOT NULL,
    [RenovatedRemark]      NVARCHAR (MAX)   NULL,
    [Developer]            NVARCHAR (50)    NULL,
    [OwnerName]            NVARCHAR (250)   NULL,
    [OwnerMobile]          NVARCHAR (250)   NULL,
    [OwnerPhone]           NVARCHAR (250)   NULL,
    [OwnerFax]             NVARCHAR (250)   NULL,
    [OwnerEmail]           NVARCHAR (250)   NULL,
    [OwnerAddress]         NVARCHAR (500)   NULL,
    [LandlordName]         NVARCHAR (250)   NULL,
    [LandlordMobile]       NVARCHAR (250)   NULL,
    [LandlordPhone]        NVARCHAR (250)   NULL,
    [LandlordFax]          NVARCHAR (250)   NULL,
    [LandlordEmail]        NVARCHAR (250)   NULL,
    [LandlordAddress]      NVARCHAR (500)   NULL,
    [ReferLister]          TINYINT          NULL,
    [ReferLandlordLister]  TINYINT          NULL,
    [RListStatus]          TINYINT          NULL,
    [SListStatus]          TINYINT          NULL,
    [RRelistStatus]        TINYINT          NULL,
    [SRelistStatus]        TINYINT          NULL,
    [StatusDelete]         TINYINT          NOT NULL,
    [CreateDate]           DATETIME         NULL,
    [UpdateDate]           DATETIME         NULL,
    [UpdateWho]            UNIQUEIDENTIFIER NULL,
    [CPPlanNo]             NVARCHAR (MAX)   NULL,
    [Floor]                NVARCHAR (MAX)   NULL,
    [CompanyName]          NVARCHAR (MAX)   NULL,
    [ViewContact]          NVARCHAR (MAX)   NULL,
    [OtherContact]         NVARCHAR (MAX)   NULL,
    [LandlordCompanyName]  NVARCHAR (MAX)   NULL,
    [LandlordViewContact]  NVARCHAR (MAX)   NULL,
    [LandlordOtherContact] NVARCHAR (MAX)   NULL,
    [TitleNo]              NVARCHAR (500)   NULL,
    [CommissionRemark]     NVARCHAR (MAX)   CONSTRAINT [DF__AW_Proper__Commi__1DB06A4F] DEFAULT ('Nil') NULL,
    [SCreateDate]          DATETIME         NULL,
    [RCreateDate]          DATETIME         NULL,
    [AgentFeedback]        INT              NULL,
    [AgentFeedbackRent]    INT              NULL,
    [SaleView]             TINYINT          CONSTRAINT [DF_AW_Property_SaleView] DEFAULT ((1)) NULL,
    [RentView]             TINYINT          CONSTRAINT [DF_AW_Property_RentView] DEFAULT ((1)) NULL,
    [Latitude]             DECIMAL (9, 6)   NULL,
    [Longitude]            DECIMAL (9, 6)   NULL,
    [SaleGST]              TINYINT          CONSTRAINT [DF_AW_Property_SaleGST] DEFAULT ((0)) NULL,
    [RentGST]              TINYINT          CONSTRAINT [DF_AW_Property_RentGST] DEFAULT ((0)) NULL,
    [SaleRemark]           NVARCHAR (2000)  NULL,
    [RentRemark]           NVARCHAR (2000)  NULL,
    CONSTRAINT [PK_AW_Property_1] PRIMARY KEY CLUSTERED ([PropertyId] ASC)
);






GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO



GO
CREATE TRIGGER [dbo].[AW_Property_dss_update_trigger] ON [dbo].[AW_Property] FOR UPDATE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 340404482 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Property_dss_tracking] AS [target] 
USING (SELECT [i].[PropertyId] FROM INSERTED AS [i]) AS source([PropertyId]) 
ON ([target].[PropertyId] = [source].[PropertyId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[PropertyId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[PropertyId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Property_dss_insert_trigger] ON [dbo].[AW_Property] FOR INSERT AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 340404482 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Property_dss_tracking] AS [target] 
USING (SELECT [i].[PropertyId] FROM INSERTED AS [i]) AS source([PropertyId]) 
ON ([target].[PropertyId] = [source].[PropertyId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[PropertyId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[PropertyId],NULL, NULL, NULL, 0, CAST(@@DBTS AS BIGINT) + 1, NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_Property_dss_delete_trigger] ON [dbo].[AW_Property] FOR DELETE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 340404482 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_Property_dss_tracking] AS [target] 
USING (SELECT [i].[PropertyId] FROM DELETED AS [i]) AS source([PropertyId]) 
ON ([target].[PropertyId] = [source].[PropertyId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 1, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[PropertyId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[PropertyId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 1, GETDATE() );